﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Jy123.BLL.Public;
using System.Data.SqlClient;

namespace Jy123.BLL.User
{
    public class Vote
    {

        /// <summary>
        /// 将DataSet转换成V_User_VoteList的List
        /// </summary>
        /// <param name="ds">传入的DataSet</param>
        /// <returns>V_User_VoteList的List</returns>
        public List<Jy123.Model.V_User_VoteList> DataTableToList(DataTable dt)
        {
            List<Jy123.Model.V_User_VoteList> modelList = new List<Jy123.Model.V_User_VoteList>();
            int rowsCount = dt.Rows.Count;
            if (rowsCount > 0)
            {
                Jy123.Model.V_User_VoteList model;
                for (int n = 0; n < rowsCount; n++)
                {
                    model = new Jy123.Model.V_User_VoteList();
                    if (dt.Rows[n]["ID"].ToString() != "")
                    {
                        model.ID = int.Parse(dt.Rows[n]["ID"].ToString());
                    }
                    model.UserName = dt.Rows[n]["UserName"].ToString();
                    model.Portrait = dt.Rows[n]["Portrait"].ToString();

                    //判断用户是否有头像
                    model.Portrait = new Jy123.BLL.User.SPace().DefaultPortrait(Convert.ToInt32(dt.Rows[n]["FUser"]));

                    model.Title = dt.Rows[n]["Title"].ToString();
                    if (dt.Rows[n]["AddTime"].ToString() != "")
                    {
                        model.AddTime = DateTime.Parse(dt.Rows[n]["AddTime"].ToString());
                    }
                    if (dt.Rows[n]["CommentCount"].ToString() != "")
                    {
                        model.CommentCount = int.Parse(dt.Rows[n]["CommentCount"].ToString());
                    }
                    if (dt.Rows[n]["FUser"].ToString() != "")
                    {
                        model.FUser = int.Parse(dt.Rows[n]["FUser"].ToString());
                    }
                    model.Intro = dt.Rows[n]["Intro"].ToString();
                    if (dt.Rows[n]["Mode"].ToString() != "")
                    {
                        model.Mode = int.Parse(dt.Rows[n]["Mode"].ToString());
                    }
                    if (dt.Rows[n]["EndTime"].ToString() != "")
                    {
                        model.EndTime = DateTime.Parse(dt.Rows[n]["EndTime"].ToString());
                    }
                    if (dt.Rows[n]["JoinCount"].ToString() != "")
                    {
                        model.JoinCount = int.Parse(dt.Rows[n]["JoinCount"].ToString());
                    }
                    if (dt.Rows[n]["VoteCount"].ToString() != "")
                    {
                        model.VoteCount = int.Parse(dt.Rows[n]["VoteCount"].ToString());
                    }
                    if (dt.Rows[n]["IsFriend"].ToString() != "")
                    {
                        model.IsFriend = int.Parse(dt.Rows[n]["IsFriend"].ToString());
                    }
                    modelList.Add(model);
                }
            }
            return modelList;
        }
        /// <summary>
        /// 分页返回好友投票
        /// </summary>
        /// <param name="userId">当前用户ID</param>
        /// <param name="currentpage">当前页</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="field">排序字段</param>
        /// <param name="sortField">排序字段</param>
        /// <param name="order">排序方式(true为ASC|false为DESC)</param>
        /// <returns>好友投票列表</returns>
        public List<Jy123.Model.V_User_VoteList> PaginationVoteList_Friend(int userId, int currentpage, int pageSize, string field, string sortField, out List<int?> counts)
        {
            Jy123.Model.MPagination mPagination = new Jy123.Model.MPagination();
            StringBuilder strFilter = new StringBuilder();
            strFilter.AppendFormat("FUser IN (SELECT FFriend FROM User_Friend WHERE FUser = {0})", userId);
            
            mPagination.Tables = "V_User_VoteList";
            mPagination.PrimaryKey = "ID";
            mPagination.Fields = field;
            mPagination.CurrentPage = currentpage;
            mPagination.Filter = strFilter.ToString();
            mPagination.PageSize = pageSize;
            mPagination.Sort = sortField;
            mPagination.Group = string.Empty;
            DataSet ds = Pagination.GetList(mPagination,out counts);
            List<Jy123.Model.V_User_VoteList> modelList = DataTableToList(ds.Tables[0]);
            return modelList;
        }
        /// <summary>
        /// 分页返回当前用户投票
        /// </summary>
        /// <param name="userId">当前用户ID</param>
        /// <param name="currentpage">当前页</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="field">查询的字段</param>
        /// <param name="sortField">排序字段</param>
        /// <param name="order">排序方式(true为ASC|false为DESC)</param>
        /// <returns>当前用户发起的投票列表</returns>
        public List<Jy123.Model.V_User_VoteList> PaginationVoteList_MY(int userId, int currentpage, int pageSize, string field, string sortField, out List<int?> counts)
        {
            Jy123.Model.MPagination mPagination = new Jy123.Model.MPagination();
            StringBuilder strFilter = new StringBuilder();
            strFilter.AppendFormat("(FUser = {0} )", userId);
            
            mPagination.Tables = "V_User_VoteList";
            mPagination.PrimaryKey = "ID";
            mPagination.Fields = field;
            mPagination.CurrentPage = currentpage;
            mPagination.Filter = strFilter.ToString();
            mPagination.PageSize = pageSize;
            mPagination.Sort = sortField;
            mPagination.Group = string.Empty;
            DataSet ds = Pagination.GetList(mPagination, out counts);
            List<Jy123.Model.V_User_VoteList> modelList = DataTableToList(ds.Tables[0]);
            return modelList;
        }
        /// <summary>
        /// 分页返回最新投票
        /// </summary>
        /// <param name="currentpage">当前页</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="field">查询的字段</param>
        /// <param name="sortField">排序字段</param>
        /// <param name="order">排序方式(true为ASC|false为DESC)</param>
        /// <returns> 最新的投票列表</returns>
        public List<Jy123.Model.V_User_VoteList> PaginationVoteList_New(int currentpage, int pageSize, string field, string sortField, out List<int?> counts)
        {
            Jy123.Model.MPagination mPagination = new Jy123.Model.MPagination();
            //StringBuilder strFilter = new StringBuilder();
            //strFilter.AppendFormat("(FUser = {0} )", userId);
            
            mPagination.Tables = "V_User_VoteList";
            mPagination.PrimaryKey = "ID";
            mPagination.Fields = field;
            mPagination.CurrentPage = currentpage;
            //mPagination.Filter = strFilter.ToString();
            mPagination.PageSize = pageSize;
            mPagination.Sort = sortField;
            mPagination.Group = string.Empty;
            DataSet ds = Pagination.GetList(mPagination,out counts);
            List<Jy123.Model.V_User_VoteList> modelList = DataTableToList(ds.Tables[0]);
            return modelList;
        }
        /// <summary>
        /// 热门投票
        /// </summary>
        /// <param name="currentpage">起始页</param>
        /// <param name="pageSize">记录大小</param>
        /// <param name="field">字段</param>
        /// <returns>list</returns>
        public List<Jy123.Model.V_User_VoteList> PaginationVoteList_Hot(int currentpage, int pageSize, string field, out List<int?> counts)
        {
            Jy123.Model.MPagination mPagination = new Jy123.Model.MPagination();
            //StringBuilder strFilter = new StringBuilder();
            //strFilter.AppendFormat("(FUser = {0} )", userId);

            mPagination.Tables = "V_User_VoteList";
            mPagination.PrimaryKey = "ID";
            mPagination.Fields = field;
            mPagination.CurrentPage = currentpage;
           //mPagination.Filter = strFilter.ToString();
            mPagination.PageSize = pageSize;
            mPagination.Sort = "JoinCount DESC";
            mPagination.Group = string.Empty;
            DataSet ds = Pagination.GetList(mPagination, out counts);
            List<Jy123.Model.V_User_VoteList> modelList = DataTableToList(ds.Tables[0]);
            return modelList;
        }
        /// <summary>
        /// 分页返回参与的投票
        /// </summary>
        /// <param name="userId">当前用户的ID</param>
        /// <param name="currentpage">当前页</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="field">查询的字段</param>
        /// <param name="sortField">排序字段</param>
        /// <param name="order">排序方式(true为ASC|false为DESC)</param>
        /// <returns>投票列表</returns>
        public List<Jy123.Model.V_User_VoteList> PaginationVoteList_Join(int userId, int currentpage, int pageSize, string field, string sortField, out List<int?> counts)
        {
            Jy123.Model.MPagination mPagination = new Jy123.Model.MPagination();
            StringBuilder strFilter = new StringBuilder();
            strFilter.AppendFormat("ID in (select FVote from User_ToVote where FUser = {0})", userId);
            
            mPagination.Tables = "V_User_VoteList";
            mPagination.PrimaryKey = "ID";
            mPagination.Fields = field;
            mPagination.CurrentPage = currentpage;
            mPagination.Filter = strFilter.ToString();
            mPagination.PageSize = pageSize;
            mPagination.Sort = sortField;
            mPagination.Group = string.Empty;
            DataSet ds = Pagination.GetList(mPagination, out counts);
            List<Jy123.Model.V_User_VoteList> modelList = DataTableToList(ds.Tables[0]);
            return modelList;
        }

        /// <summary>
        /// 投票选项列表
        /// </summary>
        /// <param name="voteID">投票ID</param>
        /// <param name="currentpage">当前页</param>
        /// <param name="pageSize">页记录大小</param>
        /// <param name="field">字段</param>
        /// <param name="sortField">排序字段</param>
        /// <returns>数据集</returns>
        public DataSet VoteOptionList(int voteID, int currentpage, int pageSize, string field, string sortField)
        {
            Jy123.Model.MPagination mPagination = new Jy123.Model.MPagination();
            StringBuilder strFilter = new StringBuilder();
            strFilter.AppendFormat("Fvote={0}",voteID );

            mPagination.Tables = "User_VoteOption";
            mPagination.PrimaryKey = "ID";
            mPagination.Fields = field;
            mPagination.CurrentPage = currentpage;
            mPagination.Filter = strFilter.ToString();
            mPagination.PageSize = pageSize;
            mPagination.Sort = sortField;
            mPagination.Group = string.Empty;
            DataSet ds = Pagination.GetList(mPagination);
            return ds;
        }
        /// <summary>
        /// 投票列表搜索    
        /// </summary>
        /// <param name="userName">用户姓名</param>
        /// <param name="title">投票标题</param>
        /// <param name="currentpage">起始页</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="field">查询字段</param>
        /// <param name="sortField">排序字段</param>
        /// <param name="counts"></param>
        /// <returns>list</returns>
        public List<Jy123.Model.V_User_VoteList> VoteList(string userName, string title, int currentpage, int pageSize, string field, string sortField, out List<int?> counts)
        {
            Jy123.Model.MPagination mPagination = new Jy123.Model.MPagination();
            StringBuilder strFilter = new StringBuilder();
            if (!string.IsNullOrEmpty(userName))
            {
                strFilter.AppendFormat("(UserName like '%{0}%' )", userName);
            }
            if (!string.IsNullOrEmpty(title))
            {
                strFilter.AppendFormat("(Title like '%{0}%')", title);
            }

            mPagination.Tables = "V_User_VoteList";
            mPagination.PrimaryKey = "ID";
            mPagination.Fields = field;
            mPagination.CurrentPage = currentpage;
            mPagination.Filter = strFilter.ToString();
            mPagination.PageSize = pageSize;
            mPagination.Sort = sortField;
            mPagination.Group = string.Empty;
            DataSet ds = Pagination.GetList(mPagination, out counts);
            List<Jy123.Model.V_User_VoteList> modelList = DataTableToList(ds.Tables[0]);
            return modelList;
        }
        /// <summary>
        /// 检查投票记录是否已存在
        /// </summary>
        /// <param name="userID">用户ID</param>
        /// <param name="voteID">投票ID</param>
        /// <param name="optionID">选项ID</param>
        /// <returns>true/false</returns>
        public int ToVoted(int userID,int voteID)
        {
            string strSql = "select count(*)as num from dbo.User_ToVote where FUser=@UserID and FVote=@VoteID ";
            SqlParameter[] parameters = { 
                                        new SqlParameter("@UserID",SqlDbType.Int),
                                        new SqlParameter("@VoteID",SqlDbType.Int)};
            parameters[0].Value = userID;
            parameters[1].Value = voteID;
            return (int)DBUtility.DbHelperSQL.GetSingle(strSql, parameters);
        }
        /// <summary>
        /// 评论内容
        /// </summary>
        /// <param name="userID">用户ID</param>
        /// <param name="voteID">投票ID</param>
        /// <returns>字符串</returns>
        public string ToVoteCommentContent(int userID, int voteID)
        {
            string strSql = "select CommentContent from dbo.User_ToVote where FUser=@UserID and FVote=@VoteID ";
            SqlParameter[] parameters = { 
                                        new SqlParameter("@UserID",SqlDbType.Int),
                                        new SqlParameter("@VoteID",SqlDbType.Int)};
            parameters[0].Value = userID;
            parameters[1].Value = voteID;
            return (string)DBUtility.DbHelperSQL.GetSingle(strSql, parameters);
        }

        public int UpdateToVoteCommentContent(int userID, int voteID,string newCommentContent)
        {
            string strSql = "update dbo.User_ToVote set CommentContent=@newCommentContent where FUser=@UserID and FVote=@VoteID ";
            SqlParameter[] parameters = { 
                                        new SqlParameter("@UserID",SqlDbType.Int),
                                        new SqlParameter("@VoteID",SqlDbType.Int),
                                        new SqlParameter("@newCommentContent",SqlDbType.NVarChar)};
            parameters[0].Value = userID;
            parameters[1].Value = voteID;
            parameters[2].Value = newCommentContent;
            return DBUtility.DbHelperSQL.ExecuteSql(strSql, parameters);
        }
        /// <summary>
        /// 根据投票ID统计投票总数
        /// </summary>
        /// <param name="voteID">投票的ID</param>
        /// <returns>数量</returns>
        public int VoteRecordCount(int voteID)
        {
            string strSql = "select count(*)as num from dbo.User_ToVote where FVote=@VoteID ";
            SqlParameter[] parameters = {
                                        new SqlParameter("@VoteID",SqlDbType.Int)};
            parameters[0].Value = voteID;
            return (int)DBUtility.DbHelperSQL.GetSingle(strSql, parameters);
        }
        /// <summary>
        /// 用户已投票选项列表
        /// </summary>
        /// <param name="userID">用户ID</param>
        /// <param name="voteID">投票ID</param>
        /// <param name="currentpage">当前页</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="field">查询的字段</param>
        /// <param name="sortField">排序字段</param>
        /// <returns>数据集</returns>
        public DataSet VotedOptionList(int userID, int voteID, int currentpage, int pageSize, string field, string sortField)
        {
            Jy123.Model.MPagination mPagination = new Jy123.Model.MPagination();
            StringBuilder strFilter = new StringBuilder();
            strFilter.Append("fuser='" + userID + "' and fvote='" + voteID + "'");
            mPagination.Tables = "V_User_VotedOptionList";
            mPagination.PrimaryKey = "ID";
            mPagination.Fields = field;
            mPagination.CurrentPage = currentpage;
            mPagination.Filter = strFilter.ToString();
            mPagination.PageSize = pageSize;
            mPagination.Sort = sortField;
            mPagination.Group = string.Empty;
            DataSet ds = Pagination.GetList(mPagination);
            return ds;
        }
        /// <summary>
        /// 投票者列表
        /// </summary>
        /// <param name="userID">用户ID</param>
        /// <param name="voteID">投票ID</param>
        /// <param name="currentpage">起始页</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="field">查询字段</param>
        /// <param name="strFilter">筛选条件</param>
        /// <param name="sortField">排序字段</param>
        /// <returns>数据集</returns>
        public DataSet VotedUserList(int userID, int voteID, int currentpage, int pageSize, string field, string strFilter, string sortField)
        {
            Jy123.Model.MPagination mPagination = new Jy123.Model.MPagination();
            //mPagination.Tables = "V_User_VotedUserList";
            mPagination.Tables = "V_User_VotedFriendList";
            mPagination.PrimaryKey = "fuser";
            mPagination.Fields = field;
            mPagination.CurrentPage = currentpage;
            mPagination.Filter = strFilter;
            mPagination.PageSize = pageSize;
            mPagination.Sort = sortField;
            mPagination.Group = string.Empty;
            DataSet ds = Pagination.GetList(mPagination);
            return ds;
        }

        /// <summary>
        /// 投票评论列表
        /// </summary>
        /// <param name="voteID">投票ID</param>
        /// <param name="currentpage">当前页</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="field">查询字段</param>
        /// <param name="strFilter">筛选条件</param>
        /// <param name="sortField">排序字段</param>
        /// <returns>数据集</returns>
        public DataSet VoteCommentList(int voteID, int currentpage, int pageSize, string field, string strFilter, string sortField)
        {
            Jy123.Model.MPagination mPagination = new Jy123.Model.MPagination();
            mPagination.Tables = "V_User_VoteCommentList";
            mPagination.PrimaryKey = "ID";
            mPagination.Fields = field;
            mPagination.CurrentPage = currentpage;
            mPagination.Filter = strFilter;
            mPagination.PageSize = pageSize;
            mPagination.Sort = sortField;
            mPagination.Group = string.Empty;
            DataSet ds = Pagination.GetList(mPagination);
            return ds;
        }
        /// <summary>
        /// 根据用户ID统计用户发出的投票数量
        /// </summary>
        /// <param name="userID">用户ID</param>
        /// <returns>投票数量</returns>
        public int GetVoteListCountByUserID(int userID)
        {
            string strSql = "select count(1) from dbo.User_Vote where fuser=@UserID";
            SqlParameter[] parameter = {
                                       new SqlParameter("@UserID",SqlDbType.Int)};
            parameter[0].Value = userID;
            return (int)DBUtility.DbHelperSQL.GetSingle(strSql, parameter);
        }
    }
}
